package MyPassage.util;

import MyPassage.model.Passage;
import MyPassage.model.Product;
import MyPassage.model.Wagon;

import java.sql.*;
import java.time.format.DateTimeFormatter;
import java.util.List;
import java.util.StringJoiner;

public final class GeneralUtils {
    private GeneralUtils(){}

//    public static List<String> getReleasedPassages() {
//        try (Connection connection = DriverManager.getConnection("jdbc:sqlite:productsDB.db");
//             Statement statement = connection.createStatement()) {
//
//            List<String> releasedTablesNames = new ArrayList<>();
//            ResultSet rs = statement.executeQuery("select name from sqlite_master");
//            while (rs.next()) releasedTablesNames.add(rs.getString("name"));
//
//            return releasedTablesNames;
//
//        } catch (SQLException e) {
//            Dialogs.create()
//                    .title("Какая-то непонятная ошибка")
//                    .masthead("Я не знаю, что за ошибка")
//                    .message("Да, косяк, но я впервые об этой ошибке слышу.\n" +
//                            "Простите.....\n" +
//                            "getReleasedPassages")
//                    .showError();
//        }
//        return new ArrayList<>();
//    }
//
//    private static boolean isPassageNameAvailable(String fullName) {
//        List<String> unavailableNames = new ArrayList<>(1000);
//
//        try (Connection connection = DriverManager.getConnection("jdbc:sqlite:productsDB.db");
//             Statement statement = connection.createStatement()) {
//
//            ResultSet rs = statement.executeQuery("select name from sqlite_master");
//            while (rs.next()) unavailableNames.add(rs.getString("name"));
//        } catch (SQLException e) {
//            Dialogs.create()
//                    .title("Какая-то непонятная ошибка")
//                    .masthead("Я не знаю, что за ошибка")
//                    .message("Да, косяк, но я впервые об этой ошибке слышу.\n" +
//                            "Простите....." +
//                            "saveToProductSQLTable")
//                    .showError();
//        }
//
//        return !unavailableNames.contains(fullName + "#BEGIN");
//    }
//    private static void createWagonBeginAndEndTables(String fullName) {
//        String newWagonName;
//
//        try (Connection connection = DriverManager.getConnection("jdbc:sqlite:productsDB.db");
//             Statement statement = connection.createStatement()) {
//            for (int i = 1; i <= 2; i++) {
//                newWagonName = fullName + (i == 1 ? "#BEGIN" : "#END");
//                statement.addBatch("DROP TABLE IF EXISTS '" + newWagonName + "'");
//                statement.addBatch(
//                        "CREATE TABLE '" + newWagonName + "' (" +
//                                "title              TEXT DEFAULT '' NOT NULL," +
//                                "count              INT DEFAULT 0 NOT NULL," +
//                                "additionalCount1   INT DEFAULT 0 NOT NULL," +
//                                "additionalCount2   INT DEFAULT 0 NOT NULL," +
//                                "additionalCount3   INT DEFAULT 0 NOT NULL," +
//                                "additionalCount4   INT DEFAULT 0 NOT NULL," +
//                                "price              INT DEFAULT 0 NOT NULL," +
//                                "ostatok            INT DEFAULT 0 NOT NULL," +
//                                "col                INT DEFAULT 0 NOT NULL" +
//                                ")"
//                );
//                connection.createStatement().addBatch("INSERT INTO '" + newWagonName + "' (title) VALUES ('" + newWagonName + "')");
//            }
//            statement.executeBatch();
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//    }
//    private static void createWagonTables(String fullName, int count) {
//        createWagonBeginAndEndTables(fullName);
//        fullName = fullName + "#wagon";
//
//        try (Connection connection = DriverManager.getConnection("jdbc:sqlite:productsDB.db");
//             Statement statement = connection.createStatement()) {
//            for (int i = 1; i <= count; i++) {
//                statement.addBatch("DROP TABLE IF EXISTS '" + fullName + i + "'");
//                statement.addBatch(
//                        "CREATE TABLE '" + fullName + i + "' (" +
//                                "title              TEXT DEFAULT '' NOT NULL," +
//                                "count              INT DEFAULT 0 NOT NULL," +
//                                "additionalCount1   INT DEFAULT 0 NOT NULL," +
//                                "additionalCount2   INT DEFAULT 0 NOT NULL," +
//                                "additionalCount3   INT DEFAULT 0 NOT NULL," +
//                                "additionalCount4   INT DEFAULT 0 NOT NULL," +
//                                "price              INT DEFAULT 0 NOT NULL," +
//                                "ostatok            INT DEFAULT 0 NOT NULL," +
//                                "col                INT DEFAULT 0 NOT NULL" +
//                                ")"
//                );
//                connection.createStatement().addBatch("INSERT INTO '" + fullName + i + "' (title) VALUES ('" + fullName + i + "')");
//            }
//            statement.executeBatch();
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//    }
//    public static boolean createNewPassage(String date, String identify, int countOfWagons) {
//        StringJoiner newPassageName =
//                new StringJoiner("#")
//                        .add(date)
//                        .add(identify);
//        if (!isPassageNameAvailable(newPassageName.toString())) return false;
//        createWagonTables(newPassageName.toString(), countOfWagons);
//        return true;
//    }
//
//    public static void removePassage(String fullName) {}/////////////////////




    private static void createPassagesList() {
        try (Connection connection = DriverManager.getConnection("jdbc:sqlite:PassageDB.db");
             Statement statement = connection.createStatement()) {
            statement.executeUpdate(
                    "CREATE TABLE IF NOT EXISTS 'Passages' (" +
                            "dateOfPassage  TEXT DEFAULT '' NOT NULL," +
                            "nameOfPassage  TEXT DEFAULT '' NOT NULL," +
                            "countOfWagons  INT DEFAULT 0 NOT NULL )");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    private static String createPassageTable(Passage passage) {
        String date = passage.getDateOfPassage().format(DateTimeFormatter.ofPattern("dd-MM-yyyy"));
        String fullName = new StringJoiner("#").add("Passage").add(date).add(passage.getName()).add(Integer.toString(passage.getWagons().size() - 1)).toString();

        try (Connection connection = DriverManager.getConnection("jdbc:sqlite:PassageDB.db");
             Statement statement = connection.createStatement()) {

            statement.execute("CREATE TABLE IF NOT EXISTS '" + fullName + "' (nameOfWagon TEXT, numberOfWagon INTEGER)");

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return fullName;
    }
    private static void createNewWagons(Passage passage) {
        String tableName = createPassageTable(passage);

        try (Connection connection = DriverManager.getConnection("jdbc:sqlite:PassageDB.db");
             PreparedStatement statement = connection.prepareStatement("INSERT INTO '" + tableName + "' VALUES (?, ?)")) {

            for (Wagon wagon : passage.getWagons()) {
                statement.setString(1, wagon.getName());
                statement.setInt(2, wagon.getNumber());
                statement.addBatch();
            }

            statement.executeBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        createNewProducts(passage);
    }
    private static void createNewProducts(Passage passage) {
        try (Connection connection = DriverManager.getConnection("jdbc:sqlite:PassageDB.db");
             Statement statement = connection.createStatement()) {

            List<Wagon> wagons = passage.getWagons();

            for (int i = 0; i < wagons.size(); i++) {
                String tableName = createWagonTable(passage, i);
                for (Product p : wagons.get(i).getProducts())
                    statement.addBatch("INSERT INTO '" + tableName + "' VALUES ('" + p.getName() + "'," + i + ")");
            }

            statement.executeBatch();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    private static String createWagonTable(Passage passage, int numberOfWagon) {

        String date = passage.getDateOfPassage().format(DateTimeFormatter.ofPattern("dd-MM-yyyy"));
        String wagonName = passage.getWagons().get(numberOfWagon).getName();
        String tableName = new StringJoiner("#").add("Wagon").add(date).add(wagonName).add(Integer.toString(numberOfWagon)).toString();

        try (Connection connection = DriverManager.getConnection("jdbc:sqlite:PassageDB.db");
             Statement statement = connection.createStatement()) {
            //  Change IT!
            statement.execute("CREATE TABLE IF NOT EXISTS '" + tableName + "' (nameOfWagon TEXT, numberOfWagon INTEGER)");

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return tableName;
    }
    public static void createNewPassage(Passage passage) {
        createPassagesList();

        try (Connection connection = DriverManager.getConnection("jdbc:sqlite:PassageDB.db");
             PreparedStatement statement = connection.prepareStatement("INSERT INTO 'Passages' VALUES (?, ?, ?)")) {

            statement.setString(1, passage.getDateOfPassage().format(DateTimeFormatter.ofPattern("dd-MM-yyyy")));
            statement.setString(2, passage.getName());
            statement.setInt(3, passage.getWagons().size() - 1);
            statement.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        createNewWagons(passage);
    }
}